﻿Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class AprobarADO
    ReadOnly MiConexion As New ConexionADO()

    Public Function pr_AprobarManifiesto(ByVal sCodVuelo As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim cmd0 As SqlCommand
            Dim cmd1 As SqlCommand
            Try
                ' se quita la cabecera del manifiesto
                cmd0 = New SqlCommand("UPDATE dbo.C_Manifiesto SET EstadoManifiesto='1' WHERE (CodViaje=@CodViaje)", cn)
                cmd0.Transaction = oTran
                cmd0.Parameters.AddWithValue("@CodViaje", sCodVuelo.Trim)
                cmd0.ExecuteNonQuery()
                ' se cambia el estado del vuelo a cancelado
                cmd1 = New SqlCommand("UPDATE V_ProgramaViaje SET EstadoViaje='2' WHERE (CodViaje=@CodViaje)", cn)
                cmd1.Transaction = oTran
                cmd1.Parameters.AddWithValue("@CodViaje", sCodVuelo.Trim)
                cmd1.ExecuteNonQuery()

                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("AprobarADO (pr_AprobarManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_CargarListaManifiestoporAprobar(ByVal sCodUser As String) As DataSet
        Try
            Dim sqlc As String = "SELECT cm.CodViaje,cm.FchViaje,lo.NomLugar as LugarOrigen,ld.NomLugar as LugarDestino,ep.NomEmpresa,CAST(0 AS BIT) AS FlgAprobar FROM dbo.C_Manifiesto cm " & _
                                 "INNER JOIN dbo.M_Lugar lo ON cm.CodLugarOrigen=lo.CodLugar " & _
                                 "INNER JOIN dbo.M_Lugar ld ON cm.CodLugarDestino=ld.CodLugar " & _
                                 "INNER JOIN dbo.M_EmpresaTransporte ep ON cm.CodEmpresa=ep.CodEmpresa " & _
                                 "WHERE(cm.EstadoManifiesto = 0) AND (cm.CodLugarOrigen IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='" & sCodUser & "')))  AND (cm.CodViaje IN (SELECT codviaje FROM dbo.d_manifiesto GROUP BY codviaje)) ORDER BY cm.FchViaje DESC"
            Dim sqld As String = "SELECT dm.CodViaje,dm.CodReserva,dm.NroDocumentoId,dm.NomPasajero+' '+dm.AppPasajero+' '+dm.ApmPasajero as Pasajero,dm.NacionalidadPasajero,dm.CodCentroCosto,dm.PctCentroCosto,re.TipPasajero,tp.NomTipoPasajero FROM dbo.D_Manifiesto dm " & _
                                 "INNER JOIN dbo.v_reserva re ON dm.CodReserva=re.CodReserva " & _
                                 "INNER JOIN dbo.m_TipoPasajero tp ON re.TipPasajero=tp.CodTipoPasajero " & _
                                 "INNER JOIN C_Manifiesto cm ON dm.CodViaje=cm.CodViaje " & _
                                 "WHERE (cm.EstadoManifiesto = 0)  AND (cm.CodLugarOrigen IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='" & sCodUser & "'))) AND (cm.CodViaje IN (SELECT codviaje FROM dbo.d_manifiesto GROUP BY codviaje))"
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim dataSet As New DataSet()
            Using cn
                cn.Open()
                Using oleDBAdapter1 As New OleDbDataAdapter(sqlc, "Provider=SQLOLEDB;" & MiConexion.GetCnx)
                    Using oleDBAdapter2 As New OleDbDataAdapter(sqld, "Provider=SQLOLEDB;" & MiConexion.GetCnx)
                        oleDBAdapter1.Fill(dataSet, "Vuelos")
                        oleDBAdapter2.Fill(dataSet, "Pasajeros")
                    End Using
                End Using
                dataSet.Relations.Add("Pasajeros", dataSet.Tables("Vuelos").Columns("CodViaje"), dataSet.Tables("Pasajeros").Columns("CodViaje"))
                Return dataSet
            End Using
        Catch myException As Exception
            MsgBox("AprobarADO (pr_CargarListaManifiestoporAprobar) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_CargarListaManifiestoporNotificar(ByVal sCodUser As String) As DataSet
        Try
            Dim sqlc As String = "SELECT c_man.CodViaje,c_man.FchViaje,c_man.HraViaje,lug.NomLugar as Origen,luga.NomLugar as Destino,emp.NomEmpresa,aero.NomAeropuerto FROM dbo.C_Manifiesto as c_man " & _
                                 "INNER JOIN dbo.M_EmpresaTransporte as emp ON c_man.CodEmpresa=emp.CodEmpresa " & _
                                 "INNER JOIN dbo.M_Lugar as lug ON c_man.CodLugarOrigen = lug.CodLugar " & _
                                 "INNER JOIN dbo.M_Lugar as luga ON c_man.CodLugarDestino= luga.CodLugar " & _
                                 "INNER JOIN dbo.M_Aeropuerto as aero ON c_man.CodLugarOrigen= aero.CodOrigen AND c_man.CodAeropuerto=aero.nroCorrelativo " & _
                                 "WHERE(c_man.EstadoManifiesto <= 1) AND (c_man.CodLugarOrigen IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='" & sCodUser & "'))) AND (c_man.CodViaje IN (SELECT a.codviaje FROM (SELECT codviaje,COUNT(*) AS RegCab FROM dbo.d_manifiesto GROUP BY codviaje) a INNER JOIN (SELECT codviaje,COUNT(*) AS RegDet FROM dbo.d_manifiesto WHERE FlgLock=0 GROUP BY codviaje) b ON a.codviaje=b.codviaje WHERE a.Regcab=b.RegDet)) ORDER BY c_man.FchViaje DESC"
            Dim sqld As String = "SELECT dm.CodViaje,dm.CodReserva,pa.NroDocumentoId,dm.NomPasajero+' '+dm.AppPasajero+' '+dm.ApmPasajero as Pasajero,dm.NacionalidadPasajero,pa.MailPasajero,pa.TlfPasajero,pa.RpcPasajero,re.TipPasajero,tp.NomTipoPasajero FROM dbo.D_Manifiesto dm " & _
                                 "INNER JOIN dbo.v_reserva re ON dm.CodReserva=re.CodReserva " & _
                                 "INNER JOIN dbo.M_Pasajero pa ON re.CodPasajero=pa.CodPasajero " & _
                                 "INNER JOIN dbo.m_TipoPasajero tp ON re.TipPasajero=tp.CodTipoPasajero " & _
                                 "INNER JOIN C_Manifiesto cm ON dm.CodViaje=cm.CodViaje " & _
                                 "WHERE (cm.EstadoManifiesto <= 1) AND (cm.CodLugarOrigen IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='" & sCodUser & "'))) AND (cm.CodViaje IN (SELECT a.codviaje FROM (SELECT codviaje,COUNT(*) AS RegCab FROM dbo.d_manifiesto GROUP BY codviaje) a INNER JOIN (SELECT codviaje,COUNT(*) AS RegDet FROM dbo.d_manifiesto WHERE FlgLock=0 GROUP BY codviaje) b ON a.codviaje=b.codviaje WHERE a.Regcab=b.RegDet))"
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim dataSet As New DataSet()
            Using cn
                cn.Open()
                Using oleDBAdapter1 As New OleDbDataAdapter(sqlc, "Provider=SQLOLEDB;" & MiConexion.GetCnx)
                    Using oleDBAdapter2 As New OleDbDataAdapter(sqld, "Provider=SQLOLEDB;" & MiConexion.GetCnx)
                        oleDBAdapter1.Fill(dataSet, "Vuelos")
                        oleDBAdapter2.Fill(dataSet, "Pasajeros")
                    End Using
                End Using
                dataSet.Relations.Add("Pasajeros", dataSet.Tables("Vuelos").Columns("CodViaje"), dataSet.Tables("Pasajeros").Columns("CodViaje"))
                Return dataSet
            End Using
        Catch myException As Exception
            MsgBox("AprobarADO (pr_CargarListamanifiestoporNotificar) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

End Class
